﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using System.IO;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Web.Security;

namespace TimeCapsule
{
	public class dbAccess
	{
		private SqlConnection conn;
		private SqlTransaction trans = null;
		protected string ConnectionString { get { return ConfigurationManager.ConnectionStrings["TimeCapsule"].ConnectionString; } }

		private void Init()
		{

		}

		public void BlogWrite(string title, string body)
		{
			SqlCommand cmd = new SqlCommand();
							// Idx, Date, Title, Body, Hit, Img
			cmd.CommandText = @"INSERT INTO BLOG 
									 VALUES (GETDATE(), @title, @body, 0, '')";

			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@title", SqlDbType.NVarChar, 1024).Value = title.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@body", SqlDbType.Text).Value = body.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			//cmd.Parameters.Add("@image", SqlDbType.Image).Value = "";

			cmd.Connection = conn;
			cmd.ExecuteNonQuery();
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}

		public void BlogRplWrite(string Id, string Body, string Psw, int Page, bool Member, int LoginIdx)
		{
			SqlCommand cmd = new SqlCommand();
								//Idx, Active, Date, Id, Body, Page, Password, Member, LoginIdx
			cmd.CommandText = @"INSERT INTO BlogRpl 
									 VALUES (1, GETDATE(), @id, @body, @page, @pwd, @member, @LoginIdx)";

			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}
			cmd.Parameters.Add("@id", SqlDbType.NVarChar, 16).Value = Id.Replace("[", "![").Replace("%", "!%").Replace("_", "!_").Replace("&", "&amp;").Replace("<", "&lt;").Replace(">","&gt;");
			cmd.Parameters.Add("@body", SqlDbType.Text).Value = Body.Replace("[", "![").Replace("%", "!%").Replace("_", "!_").Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;");
			cmd.Parameters.Add("@page", SqlDbType.Int).Value = Page;
			cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 16).Value = Psw.Replace("[", "![").Replace("%", "!%").Replace("_", "!_").Replace("&", "&amp;").Replace("<", "&lt;").Replace(">", "&gt;");
			cmd.Parameters.Add("@member", SqlDbType.Int).Value = Member ? 1 : 0;
			cmd.Parameters.Add("@LoginIdx", SqlDbType.Int).Value = LoginIdx;

			cmd.Connection = conn;
			cmd.ExecuteNonQuery();
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}

		public PagedDataSource BindBlog(int pageSize)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"SELECT Idx, Title, Date, Hit, Body 
								  FROM Blog 
								 ORDER BY Date DESC";
			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Connection = conn;

			DataSet ds = new DataSet();
			SqlDataAdapter sda = new SqlDataAdapter(cmd);
			sda.Fill(ds);

			PagedDataSource pds = new PagedDataSource();
			pds.DataSource = ds.Tables[0].DefaultView;
			pds.AllowPaging = true;
			pds.PageSize = pageSize;

			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}

			return pds;
		}

		public PagedDataSource BindBlogReply(int Page, int pageSize)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"SELECT Idx, Id, Body, Date, Active, LoginIdx
								  FROM BlogRpl 
								 WHERE Page = @page 
								   AND Active != 0";
			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@page", SqlDbType.Int).Value = Page;

			cmd.Connection = conn;

			DataSet ds = new DataSet();
			SqlDataAdapter sda = new SqlDataAdapter(cmd);
			sda.Fill(ds);

			PagedDataSource pds = new PagedDataSource();
			pds.DataSource = ds.Tables[0].DefaultView;
			pds.AllowPaging = true;
			pds.PageSize = pageSize;

			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			return pds;
		}
		
		public bool DeactivateReply(int loginIdx, int idx, Role role)
		{
			bool admin = false;
			if (role == Role.admin)
				admin = true;
				
			SqlCommand cmd = new SqlCommand();
			if (admin)
			{
				cmd.CommandText = @"UPDATE BlogRpl
									   SET Active = 0
									 WHERE idx = @idx
									   AND Active = 1";
			}
			else
			{
				cmd.CommandText = @"UPDATE BlogRpl
								   SET Active = 0
								 WHERE LoginIdx = @loginIdx
								   AND Idx = @idx
								   And Active = 1";
			}
			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}
			
			if(!admin)
				cmd.Parameters.Add("@loginIdx", SqlDbType.Int).Value = loginIdx;
			cmd.Parameters.Add("@idx", SqlDbType.Int).Value = idx;
			cmd.Connection = conn;
			int valid = Convert.ToInt32(cmd.ExecuteNonQuery());
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			if ( valid == 1 )
				return true;
			else
				return false;
		}	//로그인 회원
		
		public bool DeactivateReply(int idx, string psw)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"UPDATE BlogRpl
 								   SET Active = 0
 								 WHERE Idx = @idx
 								   AND Password = @psw 
 								   AND Active = 1";
			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@idx", SqlDbType.Int).Value = idx;
			cmd.Parameters.Add("@psw", SqlDbType.NVarChar).Value = psw;
			cmd.Connection = conn;
			//int valid = Convert.ToInt32(cmd.ExecuteScalar());
			int valid = Convert.ToInt32(cmd.ExecuteNonQuery());
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			System.Diagnostics.Debug.WriteLine("Reply Delete " + ( ( valid == 1 ) ? "Succ" : "Fail" ) );
			if( valid == 1 )
				return true;
			else
				return false;
		}		//비회원
		
		public void RegisterUser(Member member, string password)
		{
			SqlCommand cmd = new SqlCommand();
													// Idx, Active, Email, Name, Phone, Address, Date, Password, Role, LastLogin
			cmd.CommandText = @"INSERT INTO Users 
									 VALUES(0, @email, @name, @phone, @address, GETDATE(), @password, 0, null)";
			
			bool _preOpened = true;
			if(conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@email", SqlDbType.NVarChar, 128).Value = member.Email.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@name", SqlDbType.NVarChar, 32).Value = member.Name.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@phone", SqlDbType.NVarChar, 32).Value = member.Phone.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@address", SqlDbType.NVarChar, 256).Value = member.Address.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@password", SqlDbType.NVarChar, 16).Value = password.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();
			if(!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}

		public void ModifyUserInfo(Member member)
		{
			SqlCommand cmd = new SqlCommand();

			cmd.CommandText = @"UPDATE Users
								   SET Name = @name, 
									   Phone = @phone, 
									   Address = @address
								 WHERE Idx = @idx";

			bool _preOpened = true;
			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@idx", SqlDbType.Int).Value = member.Idx;
			cmd.Parameters.Add("@name", SqlDbType.NVarChar, 32).Value = member.Name.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@phone", SqlDbType.NVarChar, 32).Value = member.Phone.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@address", SqlDbType.NVarChar, 256).Value = member.Address.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}
		
		public bool CheckValidEmail(string email)
		{
			SqlCommand cmd = new SqlCommand();
			//cmd.CommandText = "SELECT COUNT(*) FROM Users WHERE Name = @name";
			cmd.CommandText = @"SELECT * FROM Users 
										WHERE Email = @mail 
										  AND Active = 1";
			
			bool _preOpened = true;
			if(conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = email.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			
			cmd.Connection = conn;
			//int cnt = cmd.ExecuteNonQuery();
			int cnt = Convert.ToInt32(cmd.ExecuteScalar());
			System.Diagnostics.Debug.WriteLine("Valid Email Check count : " + cnt);
			if(!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			if( cnt == 0 )
				return true;
			else
				return false;
		}
		
		public bool Login(string email, string password, out DataSet ds)
		{
			ds = new DataSet();
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"SELECT Idx, Name, Role, Phone, Address, AddDate, LastLogin FROM Users 
															  							 WHERE Email = @mail 
																						   AND Active = 1 
																						   AND password = @password";
			bool _preOpened = true;
			
			if(conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}
			
			cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = email.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			SqlDataAdapter adpt = new SqlDataAdapter(cmd);
			try
			{
				adpt.Fill(ds);
			}
			catch (System.Exception ex)
			{
				throw ex;
			}
			
			cmd.CommandText = @"UPDATE Users
								   SET LastLogin = GETDATE()
								 WHERE Email = @mail2";
			using (conn = new SqlConnection(this.ConnectionString))
			{
				conn.Open();

				cmd.Parameters.Add("@mail2", SqlDbType.NVarChar).Value = email.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
				
				cmd.Connection = conn;
				cmd.Transaction = trans;
				cmd.ExecuteNonQuery();
			}
			
			if(!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			
			if( ds.Tables[0].Rows.Count == 0)
				return false;
			else
				return true;
		}
		
		public void Logout(string email)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"DELETE Session
								 WHERE email = @mail";
			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = email.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();
			
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}
		
		public void registerSession(string sessionId, string mail)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"INSERT INTO Session
									 VALUES (@sess, @mail, GETDATE())";

			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}
			
			cmd.Parameters.Add("@sess", SqlDbType.NVarChar, 24).Value = sessionId.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@mail", SqlDbType.NVarChar, 128).Value = mail.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();

			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}
		
		public void updateSession(string sessionId, string email)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"UPDATE Session
								   SET sessionId = @sess,
									   regTime = GETDATE()
								 WHERE email = @mail";

			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@sess", SqlDbType.NVarChar, 24).Value = sessionId.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Parameters.Add("@mail", SqlDbType.NVarChar, 128).Value = email.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();

			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}
		
		public DataSet GetUserProfileBySessionId(string sessionId)
		{
			DataSet ds = new DataSet();
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"SELECT Name, LastLogin
								  FROM Users
								  JOIN Session ON ( Users.email = Session.email )
								 WHERE Session.sessionId = @sess";
			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@sess", SqlDbType.NVarChar, 24).Value = sessionId.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			SqlDataAdapter adpt = new SqlDataAdapter(cmd);
			try
			{
				adpt.Fill(ds);
			}
			catch(Exception ex)
			{
				throw ex;
			}
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			return ds;
		}
		
		public void AddTempUserRegisterKey(string mail, string key)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"INSERT INTO TempUsers
									 VALUES (@key, @mail, GETDATE(), 0)";
			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}
			
			cmd.Parameters.Add("@key", SqlDbType.NVarChar, 20).Value = key;
			cmd.Parameters.Add("@mail", SqlDbType.NVarChar, 128).Value = mail.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();

			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}
		
		public DataSet GetTempUser(string key)
		{
			DataSet ds = new DataSet();
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"SELECT Email
								  FROM TempUsers
								 WHERE AuthKey = @key
								   AND Active = 0";
			
			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@key", SqlDbType.NVarChar, 20).Value = key;
			cmd.Connection = conn;
			SqlDataAdapter adpt = new SqlDataAdapter(cmd);
			try
			{
				adpt.Fill(ds);
			}
			catch (Exception ex)
			{
				throw ex;
			}
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
			return ds;
		}
		
		public void ActivateUser(string mail)
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = @"UPDATE Users
								   SET Active = 1
								 WHERE email = @mail";

			bool _preOpened = true;

			if (conn == null || conn.State != ConnectionState.Open)
			{
				_preOpened = false;
				conn = new SqlConnection(this.ConnectionString);
				conn.Open();
			}

			cmd.Parameters.Add("@mail", SqlDbType.NVarChar, 128).Value = mail.Replace("[", "![").Replace("%", "!%").Replace("_", "!_");
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();

			cmd.CommandText = @"UPDATE TempUsers
								   SET Active = 1
								 WHERE email = @mail";
								
			cmd.Connection = conn;
			cmd.ExecuteNonQuery();
			
			if (!_preOpened)
			{
				conn.Close();
				conn.Dispose();
			}
		}
	}
}
